update pa_attendance_code
set att_code='Q',
    description='Weekly Att Quiz'
where attendance_code_id=107    
/

CREATE OR REPLACE PACKAGE BODY PA_OWNER."PKG_LOG"
AS
    PROCEDURE get_log_types(p_include_special NUMBER,
                            p_result OUT SYS_REFCURSOR)
    IS
    BEGIN
        OPEN p_result FOR
        SELECT log_type_id, description, special_flag
        FROM pa_log_type
        WHERE special_flag<=p_include_special
        ORDER BY special_flag, description;
    END get_log_types;

    PROCEDURE get_log_entries(p_student_id NUMBER,
                              p_log_type   NUMBER,
                              p_result OUT SYS_REFCURSOR)
    AS
    BEGIN
        OPEN p_result FOR
        SELECT l.log_id, l.teacher_id, l.student_id, l.log_date, l.entry, l.log_type_id,
               t.lastfirst teacher_name, lt.description log_type
        FROM pa_log l JOIN pa_teacher t ON l.teacher_id=t.teacher_id
                      JOIN PA_LOG_TYPE LT ON L.LOG_TYPE_ID=LT.LOG_TYPE_ID
                      --JOIN PA_SEMESTER S ON TRUNC(L.LOG_DATE) BETWEEN S.FIRSTDAY AND S.LASTDAY
                      --                  AND TRUNC(SYSDATE) BETWEEN s.firstday AND s.lastday
        WHERE l.student_id=p_student_id
          AND l.log_type_id=DECODE(p_log_type,0,l.log_type_id,p_log_type)
        ORDER BY log_date DESC;
    END get_log_entries;

    PROCEDURE add_log_entry(p_section_id  NUMBER,
                            p_student_id  NUMBER,
                            p_entry       CLOB,
                            p_log_type_id NUMBER)
    AS
        v_entry VARCHAR2(200):=SUBSTR(p_entry,1,200);
    BEGIN
        INSERT INTO pa_log(log_id, teacher_id, student_id, log_date,
                           entry, log_type_id, schoolid, section_name)
        SELECT seq_log.NEXTVAL, s.teacher_id, p_student_id, SYSDATE,
               v_entry, p_log_type_id, pkg_school.ID, c.course_name
        FROM pa_section s JOIN pa_course c ON s.course_id=c.course_id
        WHERE s.section_id=p_section_id;

    END add_log_entry;

    PROCEDURE add_log_entry(p_student_id  NUMBER,
                            p_teacher_id  NUMBER,
                            p_entry       CLOB,
                            p_log_type_id NUMBER,
                            p_log_id OUT  NUMBER)
    AS
    BEGIN
        INSERT INTO pa_log(log_id, teacher_id, student_id, log_date,
                           entry, log_type_id, schoolid)
        VALUES(seq_log.NEXTVAL, p_teacher_id, p_student_id, SYSDATE,
               p_entry, p_log_type_id, pkg_school.ID)
        RETURNING log_id INTO p_log_id;
    END add_log_entry;

    PROCEDURE delete_log_entry(p_log_id     NUMBER,
                               p_teacher_id NUMBER)
    IS
        v_is_admin    NUMBER;
        v_call_center NUMBER;
    BEGIN
        SELECT is_admin, is_call_center
        INTO v_is_admin, v_call_center
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        IF v_call_center=1 THEN
            RAISE_APPLICATION_ERROR(-20010, 'Call Center User is not allowed to delete log entries');
        END IF;

        IF v_is_admin=0 THEN --only own entries can be deleted
            DELETE FROM pa_log
            WHERE log_id=p_log_id
              AND teacher_id=p_teacher_id;

            IF SQL%ROWCOUNT=0 THEN
                RAISE_APPLICATION_ERROR(-20011, 'Invalid Log ID or Attempt to delete somebody elses entry');
            END IF;
        ELSE
            DELETE FROM pa_log
            WHERE log_id=p_log_id;

            IF SQL%ROWCOUNT=0 THEN
                RAISE_APPLICATION_ERROR(-20012, 'Invalid Log ID');
            END IF;
        END IF;
    END delete_log_entry;

    PROCEDURE update_log_entry(p_log_id     NUMBER,
                               p_teacher_id NUMBER,
                               p_entry      CLOB,
                               p_log_type_id NUMBER)
    IS
        v_entry     VARCHAR2(200):=SUBSTR(p_entry,1,200);
        v_is_admin  NUMBER;
    BEGIN
        SELECT is_admin
        INTO v_is_admin
        FROM pa_teacher
        WHERE teacher_id=p_teacher_id;

        IF v_is_admin=0 THEN --only own entries can be updated
            UPDATE pa_log
            SET entry=v_entry,
                log_type_id=p_log_type_id,
                dcid=NULL
            WHERE log_id=p_log_id
              AND teacher_id=p_teacher_id;

            IF SQL%ROWCOUNT=0 THEN
                RAISE_APPLICATION_ERROR(-20011, 'Invalid Log ID or Attempt to update somebody elses entry');
            END IF;
        ELSE
            UPDATE pa_log
            SET entry=v_entry,
                log_type_id=p_log_type_id,
                dcid=NULL
            WHERE log_id=p_log_id;

            IF SQL%ROWCOUNT=0 THEN
                RAISE_APPLICATION_ERROR(-20012, 'Invalid Log ID');
            END IF;
        END IF;
    END update_log_entry;
END pkg_log;
/

CREATE OR REPLACE PACKAGE BODY PA_OWNER."PKG_STUDENT"
AS
    PROCEDURE get_details(p_student_id NUMBER,
                          p_result OUT SYS_REFCURSOR)
    AS
    BEGIN
       OPEN p_result FOR
        SELECT s.*, NVL(x.father, x.mother) parent_name,
               COALESCE(x.fatherdayphone,x.father_home_phone,x.motherdayphone,x.mother_home_phone) parent_phone,
               x.email_guard_pers parent_email    
        FROM pa_student s LEFT JOIN pa_student_ext x ON s.student_id=x.student_id
        WHERE s.student_id=p_student_id;
    END get_details;

    PROCEDURE get_log_entries(p_student_id NUMBER,
                              p_result OUT SYS_REFCURSOR)
    AS
    BEGIN
        OPEN p_result FOR
        SELECT l.log_id, l.teacher_id, l.student_id, l.log_date, l.entry, l.log_type_id,
               t.lastfirst teacher_name, lt.description log_type, l.section_name
        FROM pa_log l JOIN pa_teacher t ON l.teacher_id=t.teacher_id
                      JOIN PA_LOG_TYPE LT ON L.LOG_TYPE_ID=LT.LOG_TYPE_ID
                      --JOIN PA_SEMESTER S ON TRUNC(L.LOG_DATE) BETWEEN S.FIRSTDAY AND S.LASTDAY
                      --                  AND TRUNC(SYSDATE) BETWEEN s.firstday AND s.lastday
        WHERE l.student_id=p_student_id
        ORDER BY log_date DESC;
    END get_log_entries;

    PROCEDURE save_email(p_teacher_id NUMBER,
                         p_student_id NUMBER,
                         p_email_address VARCHAR2,
                         p_email_subject VARCHAR2,
                         p_email_body CLOB)
    AS
        v_log_id NUMBER;
    BEGIN
        SAVEPOINT A;
        pkg_log.add_log_entry(p_student_id=>p_student_id,
                              p_teacher_id=>p_teacher_id,
                              p_entry=>SUBSTR(p_email_body,1,200),
                              p_log_type_id=>pkg_log.EMAIL_TYPE,
                              p_log_id=>v_log_id);

        INSERT INTO pa_email_log(log_id,email_subject,email_address,message_body)
        VALUES(v_log_id,p_email_subject,p_email_address,p_email_body);
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO A;
            RAISE;
    END save_email;

    PROCEDURE save_call (p_teacher_id NUMBER,
                         p_student_id NUMBER,
                         p_message    CLOB)
    AS
        v_log_id NUMBER;
    BEGIN
        SAVEPOINT A;
        pkg_log.add_log_entry(p_student_id=>p_student_id,
                              p_teacher_id=>p_teacher_id,
                              p_entry=>SUBSTR(p_message,1,200),
                              p_log_type_id=>pkg_log.PHONE_CALL_TYPE,
                              p_log_id=>v_log_id);

        INSERT INTO pa_email_log(log_id ,message_body)
        VALUES(v_log_id,p_message);
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO A;
            RAISE;
    END save_call;

    PROCEDURE excuse_absence(p_teacher_id NUMBER,
                             p_student_id NUMBER,
                             p_start_date DATE,
                             p_memo       VARCHAR2)
    AS
        v_att_code_id NUMBER:=pkg_attendance.excused_code_id(p_start_date);

        CURSOR c IS
        SELECT e.enrollment_id
        FROM pa_enrollment e JOIN pa_section s ON e.section_id=e.section_id
                                              AND e.student_id=p_student_id
        WHERE s.semester_id=pkg_semester.semester_by_date;
    BEGIN
        SAVEPOINT A;
        FOR v IN c LOOP
            pkg_attendance.set_default_attendance
                                  (p_section_id=>NULL,
                                   p_enrollment_id=>v.enrollment_id,
                                   p_att_code_id=>v_att_code_id,
                                   p_memo=>p_memo,
                                   p_from_date=>week_start(p_start_date),
                                   p_to_date=>week_end(p_start_date)
                                   );
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO A;
            RAISE;
    END excuse_absence;
END pkg_student;
/